﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CommClass;


namespace DMS
{
    public partial class FrmLJJDOImport : DMS.FrmTemplate
    {
        DataConnection dc = new DataConnection();
        DataSource dsExcelFile ;
        List<string> ErrList = new List<string>();

        public FrmLJJDOImport()
        {
            InitializeComponent();
        }


        private void FrmDOImport_Load(object sender, EventArgs e)
        {

        }

        private void btnRead_Click(object sender, EventArgs e)
        {
            DataTable dt = dsExcelFile.GetRecord("select * from [" + cbbSheets.Text + "]").Tables[0];
            dgvDOQty.DataSource = dt;
        }


        private void btnImport_Click(object sender, EventArgs e)
        {
            DeliveryOrder _do = null;
            DOEntry de;
            string DoNumber, prodCode;
            //double SentQty;
            int rowIndex;
            string tempDONumber;
            Store LJJStore = null;
            Product LJJProd;
            ErrList.Clear();

            lbError.Items.Clear();
            //数据预处理，删除前5行无用数据
            //for (int i=0; i < 5; i++)
            //{
            //    dgvDOQty.Rows.Remove(dgvDOQty.Rows[0]);
            //}
            //按提单号排序
            dgvDOQty.Sort(dgvDOQty.Columns[1], ListSortDirection.Ascending);
            DoNumber = "";

            rowIndex = 0;
            while (rowIndex < dgvDOQty.RowCount)
            {
                //提单号为空跳过
                if (dgvDOQty.Rows[rowIndex].Cells[1].Value == null)
                {
                    dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                    ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 提单号为空－忽略");
                    rowIndex++;
                    continue;
                }

                tempDONumber = dgvDOQty.Rows[rowIndex].Cells[1].Value.ToString().Substring(0, 8);
                //当提单号改变时代表下一提单开始
                if (DoNumber != tempDONumber)
                {
                    if (_do != null)
                    {
                        //提单内容提交到数据库
                        App.DOSave(_do);
                    }

                    //判断提单号是否存在
                    if (App.DOGetByNumber(tempDONumber) != null)  //提单号已存在
                    {
                        dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                        ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 提单号已存在－忽略");
                        rowIndex++;
                        while ((rowIndex < dgvDOQty.RowCount) && (tempDONumber == dgvDOQty.Rows[rowIndex].Cells[1].Value.ToString().Substring(0, 8)))  //后续此提单号全部忽略
                        {
                            dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                            ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 提单号已存在－忽略");
                            rowIndex++;
                            continue;
                        }
                        continue;
                    }
                    //判断门店编号是否存在
                    LJJStore = App.StoreGetByCode(dgvDOQty.Rows[rowIndex].Cells[8].Value.ToString());
                    if (LJJStore == null)
                    {
                        dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                        ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 此门店编号不存在，－此张提单忽略");
                        rowIndex++;
                        while (rowIndex < dgvDOQty.RowCount)
                        {
                            if (tempDONumber == dgvDOQty.Rows[rowIndex].Cells[1].Value.ToString().Substring(0, 8))  //后续此提单号全部忽略
                            {
                                dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                                ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 此门店编号不存在，－此张提单忽略");
                                rowIndex++;
                                continue;
                            }
                        }
                        continue;
                    }

                    DoNumber = tempDONumber;
                    //生成提单头
                    _do = new DeliveryOrder();
                    _do.SupplierID = 4;  //供货商代码 4－李锦记
                    _do.DeliveryNumber = DoNumber;
                    _do.OrderNumber = "";
                    _do.OrderType = "";
                    _do.SaleType = "直销";
                    _do.PONumber = dgvDOQty.Rows[rowIndex].Cells[6].Value.ToString();
                    try
                    {
                        _do.OrderDatetime = DateTime.ParseExact(dgvDOQty.Rows[rowIndex].Cells[5].ToString(), "yyyyMMdd", System.Globalization.CultureInfo.CurrentCulture);
                    }
                    catch 
                    {
                        //转换错误则忽略，采用默认值
                    }
                    _do.SoldTo = dgvDOQty.Rows[rowIndex].Cells[9].Value.ToString();
                    _do.DeliveryTo = dgvDOQty.Rows[rowIndex].Cells[9].Value.ToString();
                    _do.CustName = dgvDOQty.Rows[rowIndex].Cells[9].Value.ToString();
                    _do.DeliveryAddress = dgvDOQty.Rows[rowIndex].Cells[22].Value.ToString();
                    _do.Remark = dgvDOQty.Rows[rowIndex].Cells[21].Value.ToString();
                    _do.CustCode = LJJStore.StoreCode;
                    _do.AreaID = LJJStore.AreaID;
                    _do.CarrierID = LJJStore.CarrierID;
                    _do.BusinessMan = dgvDOQty.Rows[rowIndex].Cells[3].Value.ToString();
                }
                //生成提单明细项
                //取得产品代码
                prodCode = dgvDOQty.Rows[rowIndex].Cells[10].Value.ToString();
                //通过产品代码取得产品ID
                LJJProd = App.ProductGetByCode(prodCode);
                //判断产品在数据库中是否存在
                if (LJJProd == null)   //产品在数据库中不存在
                {
                    _do = null; //此张提单全部不保存
                    dgvDOQty.Rows[rowIndex].DefaultCellStyle.BackColor = Color.Red;
                    ErrList.Add("序号:" + dgvDOQty.Rows[rowIndex].Cells[0].Value.ToString() + ": 产品代码不存在－此张提单忽略");
                    rowIndex++;
                    while ((rowIndex < dgvDOQty.RowCount) && (tempDONumber == dgvDOQty.Rows[rowIndex].Cells[1].Value.ToString().Substring(0, 8)))  //后续此提单号全部忽略
                    {
                        rowIndex++;
                    }
                    continue;
                }


                de = new DOEntry();
                de.ProdID = LJJProd.ProdID;
                de.OrderQty = ValueToDouble(dgvDOQty.Rows[rowIndex].Cells[13].Value) / LJJProd.UnitQty;
                de.Price = ValueToDouble(dgvDOQty.Rows[rowIndex].Cells[16].Value) * LJJProd.UnitQty;
                de.NoTaxAmt = ValueToDouble(dgvDOQty.Rows[rowIndex].Cells[17].Value);
                de.Tax = ValueToDouble(dgvDOQty.Rows[rowIndex].Cells[18].Value);
                de.Amount = ValueToDouble(dgvDOQty.Rows[rowIndex].Cells[19].Value);
                _do.Items.Add(de);
                rowIndex++;
            }
            //表格全部读完，最后一条记录保存
            if (_do != null)
            {
                //提单内容提交到数据库
                App.DOSave(_do);
            }


            if (ErrList.Count > 0)
            {
                foreach (string str in ErrList)
                {
                    lbError.Items.Add(str);
                }
                tcInfo.SelectedIndex = 1;
            }
            MessageBox.Show("提单导入完成！");
        }

        //字符串转换为双精度实数
        private double ValueToDouble(object value)
        {
            if ((value == DBNull.Value) || App.IsNumeric(value.ToString()))
            {
                return 0;
            }
            else
            {
                return Convert.ToDouble(value);
            }
        }

        private void btnFindImportFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog dlg = new OpenFileDialog();

            dlg.Filter = "Excel文件|*.xls;*.xlsx|所有文件|*.*";

            if (dlg.ShowDialog() == DialogResult.OK)
            {
                this.tbFileName.Text = dlg.FileName;
                dlg.Dispose();
                dc.DBName = tbFileName.Text;
                dsExcelFile = new ExcelDataSource(dc);
                //(dsExcelFile as ExcelDataSource).HasHeader = true; //将第一行做为表头
                cbbSheets.DataSource = dsExcelFile.GetTableNames();
                cbbSheets.SelectedIndex = 0;
            }

        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            Close();
        }
    }
}
